package connectDB;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import org.w3c.dom.views.AbstractView;

import entities.Customerentities;
import entities.Faqentities;
import entities.Packetentities;

public class CustomerDB extends AbstractDB implements IDB<Customerentities, Boolean,String> {

	@Override
	public Boolean add(Customerentities e) {
		String sql = "insert into customer values(?,?,?,?,?,?,?,?,?)";
		Connection con =this.condb.getConnectDB();
		PreparedStatement pst;

		try {
			pst=con.prepareStatement(sql);
			
			pst.setString(1, e.getName());
			pst.setString(2, e.getCardid());
			pst.setInt(3,e.getPacketid() );
			pst.setString(4,e.getPhone() );
			pst.setString(5,e.getEmail() );
			pst.setString(6,e.getAddress() );
			pst.setString(7,e.getServiceday() );
			pst.setDouble(8,e.getPaypermonth());
			pst.setString(9,e.getUserid() );
			
			return pst.executeUpdate() > 0;
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		return false;
	}

	@Override
	public Boolean delete(String k) {
		return null;
	}

	@Override
	public Boolean delete1(int i) {
		String sql = "delete from customer where id="+i+"";
		Connection con =this.condb.getConnectDB();
		try {
			Statement st = con.createStatement();
			return st.executeUpdate(sql) >0;
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return false;
	}

	@Override
	public Boolean update(Customerentities e) {
		String sql = "update customer set name=?,cardid=?,packageid=?,phone=?,email=?,address=?,servicedate=?,paymonthly=?,userid=? where id=?";
		Connection con =this.condb.getConnectDB();
		PreparedStatement pst;

		try {
			pst=con.prepareStatement(sql);

			pst.setString(1, e.getName());
			pst.setString(2, e.getCardid());
			pst.setInt(3,e.getPacketid() );
			pst.setString(4,e.getPhone() );
			pst.setString(5,e.getEmail() );
			pst.setString(6,e.getAddress() );
			pst.setString(7,e.getServiceday() );
			pst.setDouble(8,e.getPaypermonth());
			pst.setString(9,e.getUserid() );
			pst.setInt(10, e.getId());
			
			
			return pst.executeUpdate() > 0;
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		return false;

	}

	@Override
	public Customerentities find(String k) {
		String sql="select * from customer where id=?";
		Connection con = this.condb.getConnectDB();
		PreparedStatement pst;
		Customerentities ld = null;
		try {
			pst= con.prepareStatement(sql);
			pst.setString(1, k);
			ResultSet rs = pst.executeQuery();
			if(rs.next())
			{
				
				int idd=rs.getInt("id");
				String name=rs.getString("name");
				String cardid=rs.getString("cardid");
				int packid=rs.getInt("packageid");
				String phone=rs.getString("phone");
				String email=rs.getString("email");
				String add=rs.getString("address");
				String serdate=rs.getString("servicedate");
				double pay=rs.getDouble("paymonthly");
				String userid=rs.getString("userid");
				
				ld=  new Customerentities(idd, name, cardid, packid, phone, email, add, serdate, pay, userid);	
			}
			rs.close();
			pst.close();
			con.close();
		} catch (SQLException e) {
		
			e.printStackTrace();
		}

		return ld;
	}

	@Override
	public List<Customerentities> findAll() {

		Connection con = this.condb.getConnectDB();
		String sql= "select * from customer";
		Customerentities faq=null;
		List<Customerentities> ls= new ArrayList();
		
		try {
			Statement st= con.createStatement();
			ResultSet rs= st.executeQuery(sql);
			while(rs.next()){
				
				
				int id=rs.getInt("id");
				String name=rs.getString("name");
				String cardid=rs.getString("cardid");
				int packid=rs.getInt("packageid");
				String phone=rs.getString("phone");
				String email=rs.getString("email");
				String add=rs.getString("address");
				String serdate=rs.getString("servicedate");
				double pay=rs.getDouble("paymonthly");
				String userid=rs.getString("userid");
				
				
				faq = new Customerentities(id, name, cardid, packid, phone, email, add, serdate, pay, userid);
				
				ls.add(faq);
			}
			return ls;		
		    
		} catch (SQLException e) {
		
			e.printStackTrace();
		}		
		return null;
		
	}


@Override
public Customerentities find1(int k) {
	return null;
}


//Phan Trang ..............................................................




		public List<Customerentities> findvspaging (int start, int end){
			
			String sql = "select * from (select ROW_NUMBER() over (order by  id asc) as row"
					+", * from customer) AS LogWithRowNumbers "
					+"where row>="+start+" and row<="+end;
			
			Connection con = this.condb.getConnectDB();
			List<Customerentities> listld = new ArrayList<Customerentities>();
			try {
				Statement st = con.createStatement();
				ResultSet rs = st.executeQuery(sql);
				while(rs.next())
				{
					int id=rs.getInt("id");
					String name=rs.getString("name");
					String cardid=rs.getString("cardid");
					int packid=rs.getInt("packageid");
					String phone=rs.getString("phone");
					String email=rs.getString("email");
					String add=rs.getString("address");
					String serdate=rs.getString("servicedate");
					double pay=rs.getDouble("paymonthly");
					String userid=rs.getString("userid");

					
					Customerentities cs = new Customerentities(id, name, cardid, packid, phone, email, add, serdate, pay, userid);
					listld.add(cs);
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			
			return listld;
		}
		
		// lay tong so dong ..............
		public int findtotalrows()
		{
			int count=0;
			String sql="SELECT COUNT(id)as Totalrow FROM customer";
			Connection con = this.condb.getConnectDB();
			try {
				Statement st = con.createStatement();
				ResultSet rs = st.executeQuery(sql);
				if(rs.next())
				{
					count =Integer.parseInt(rs.getString(1));
					//count = rs.getInt(1);
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			
			
			return count;
		}
//het phan trang
		
		public static void main(String[] args) {
			CustomerDB x = new CustomerDB();
			Customerentities y = new Customerentities(4, "hello1", "1110000000101",267,"0974678816", "ducnghia@gmail.com","HCM","2014-03-21",355, "user"	);
			System.out.println(x.add(y));
			
			//System.out.println(x.update(y));
			//System.out.println(x.delete1(4));
			//System.out.println(x.find("3"));
			List<Customerentities> listst= new ArrayList();
			
			/*listst= x.findAll();
		          for(Customerentities std : listst){
				System.out.println(std);
			}*/
			
			System.out.println("Total rows in DB :" + x.findtotalrows());


			for(Customerentities j : x.findvspaging(1, 5))
			{
				System.out.println(j.getId());
				System.out.println(j.getName());
			}
		}

}
